﻿Imports DTO
Imports System.Data.SqlClient

Public Class CustomerDAO

    Public Sub InsertCustomer(ByVal fName As String, ByVal lName As String, ByVal phone As String, ByVal passPort As String)
        Try
            Dim strSQL As String = String.Format("INSERT INTO Customer (FName, LName, Phone, Passport) VALUES ('{0}', '{1}', '{2}', '{3}')", fName, lName, phone, passPort)
            Connection.ExcuteQuery(strSQL)
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Function GetCustomerIdMax() As Integer
        Dim result As Integer = 0
        Try
            Dim sql As String = String.Format("SELECT top 1 CustomerID  FROM Customer order by CustomerID desc")
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                result = reader("CustomerID")
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return result
    End Function

    Public Function GetCustomerByCustomerId(ByVal customerId As Integer) As CustomerDTO
        Dim result As New CustomerDTO
        Try
            Dim sql As String = String.Format("SELECT *  FROM Customer where CustomerID={0}", customerId)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                result.CustomerID = reader("CustomerID")
                result.FName = reader("FName")
                result.LName = reader("LName")
                result.Phone = reader("Phone")
                result.Passport = reader("Passport")
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return result
    End Function

    Public Sub Update(ByVal customerId As Integer, ByVal fName As String, ByVal lName As String, ByVal phone As String, ByVal passPort As String)
        Try
            Dim sSQL As String = "UPDATE Customer SET "
            sSQL = String.Format("{0} FName='{1}', LName='{2}', Phone='{3}', Passport='{4}' ", sSQL, fName, lName, phone, passPort)
            'sSQL = String.Format("{0} ", sSQL)
            sSQL = String.Format("{0} Where CustomerID={1} ", sSQL, customerId)
            Connection.ExcuteQuery(sSQL)
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            Throw ex
        End Try
    End Sub
End Class
